package com.antexpress.util;
import com.antexpress.dao.AreaDAO;
import com.antexpress.dao.ExpressDistributionDAO;
import com.antexpress.pojo.ExpressDistribution;
import com.antexpress.pojo.MapCity;
import com.antexpress.pojo.MapDistrict;
import com.antexpress.pojo.MapProvince;
import com.antexpress.pojo.MapTown;
import java.io.File;  
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Date;
import java.util.logging.Level;
import java.util.logging.Logger;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;


public class ExcelUtil implements Runnable{
    private final String filePath;
    private final String fileName;
    private final AreaDAO areaDAO;
    private final ExpressDistributionDAO expDAO;
    private final File file;

    public ExcelUtil(String filePath, String fileName, AreaDAO areaDAO, ExpressDistributionDAO expDAO, File file) {
        this.filePath = filePath;
        this.fileName = fileName;
        this.areaDAO = areaDAO;
        this.expDAO = expDAO;
        this.file = file;
    }

    
    //excel-2007+version
    public void readXlsx(String filePath,String fileName,File file,AreaDAO areaDAO,ExpressDistributionDAO expDAO) throws FileNotFoundException, IOException{
        //if the file will not make the exception about the memory
//        try{
            FileInputStream fis = new FileInputStream(file);//create input stream
            XSSFWorkbook workbook = new XSSFWorkbook(fis);//create workbook
            XSSFSheet sheet = workbook.getSheetAt(0);//get the data of the first sheet
            System.out.println("打开了第一个工作表");
            
            //the list of area which need to insert into the tables
            ArrayList<MapProvince> proList = new ArrayList<>();
            ArrayList<MapCity> cityList = new ArrayList<>();
            ArrayList<MapDistrict> disList = new ArrayList<>();
            ArrayList<MapTown> townList = new ArrayList<>();
            ArrayList<ExpressDistribution> expList = new ArrayList<>();
            
            System.out.println("空白表列构建完毕");
            MapTown lastTown = null;
            for(int i = 0;i<=sheet.getLastRowNum();i++){
                XSSFRow row = sheet.getRow(i);
                System.out.println("正在处理第"+(i+1)+"行数据");
                //get the infomation from the excel file
                XSSFCell cellProvince = row.getCell(0);
                XSSFCell cellCity = row.getCell(1);
                XSSFCell cellDistrict = row.getCell(2);
                XSSFCell cellTown = row.getCell(3);
                cellProvince.setCellType(CellType.STRING);
                cellCity.setCellType(CellType.STRING);
                cellDistrict.setCellType(CellType.STRING);
                cellTown.setCellType(CellType.STRING);

                //compare
                Date date = new Date();
                if(lastTown!=null){
                    //get last area infomation
                    MapDistrict lastDis = lastTown.getMapDistrict();
                    MapCity lastCity = lastDis.getMapCity();
                    MapProvince lastProvince = lastCity.getMapProvince();
                    //the same town,do nothing
                    if(lastTown.getName().equals(cellTown.getStringCellValue())){}
                    //the town is different
                    else{
                        //only the town is different
                        if(lastDis.getName().equals(cellDistrict.getStringCellValue())){
                            MapTown town = new MapTown(DataUtil.getUUID(), lastDis, cellTown.getStringCellValue(), date, date);
                            ExpressDistribution exp = new ExpressDistribution(DataUtil.getUUID(), town, date, date);
                            expList.add(exp);
                            townList.add(town);
                            lastTown = town;
                        }
                        //thie district is different
                        else{
                            //the district and town are different
                            if(lastCity.getName().equals(cellCity.getStringCellValue())){
                                MapDistrict district = new MapDistrict(DataUtil.getUUID(), lastCity, cellDistrict.getStringCellValue(), date, date);
                                MapTown town = new MapTown(DataUtil.getUUID(), district, cellTown.getStringCellValue(), date, date);
                                ExpressDistribution exp = new ExpressDistribution(DataUtil.getUUID(), town, date, date);
                                expList.add(exp);
                                disList.add(district);
                                townList.add(town);
                                lastTown = town;
                            }
                            //the city is different
                            else{
                                //the city, district and town are different
                                if(lastProvince.getName().equals(cellProvince.getStringCellValue())){
                                    MapCity city = new MapCity(DataUtil.getUUID(), lastProvince, cellCity.getStringCellValue(), date, date);
                                    MapDistrict district = new MapDistrict(DataUtil.getUUID(), city, cellDistrict.getStringCellValue(), date, date);
                                    MapTown town = new MapTown(DataUtil.getUUID(), district, cellTown.getStringCellValue(), date, date);
                                    ExpressDistribution exp = new ExpressDistribution(town.getId(), town, date, date);
                                    expList.add(exp);
                                    cityList.add(city);
                                    disList.add(district);
                                    townList.add(town);
                                    lastTown = town;
                                }
                                //all are different,insert the info of the province
                                else{
                                    areaDAO.addProvince(proList);
                                    proList.clear();
                                    areaDAO.addCity(cityList);
                                    cityList.clear();
                                    areaDAO.addDistrict(disList);
                                    disList.clear();
                                    areaDAO.addTown(townList);
                                    townList.clear();
                                    areaDAO.addDistrict(disList);
                                    disList.clear();
                                    expDAO.addExpressDistribution(expList);
                                    expList.clear();

                                    MapProvince province = new MapProvince(DataUtil.getUUID(),cellProvince.getStringCellValue(),date,date);
                                    MapCity city = new MapCity(DataUtil.getUUID(), province, cellCity.getStringCellValue(), date, date);
                                    MapDistrict district = new MapDistrict(DataUtil.getUUID(), city, cellDistrict.getStringCellValue(), date, date);
                                    MapTown town = new MapTown(DataUtil.getUUID(), district, cellTown.getStringCellValue(), date, date);
                                    ExpressDistribution exp = new ExpressDistribution(town.getId(), town, date, date);
                                    expList.add(exp);
                                    proList.add(province);
                                    cityList.add(city);
                                    disList.add(district);
                                    townList.add(town);
                                    lastTown = town;
                                }
                            }
                        }
                    }
                }
                else{
                    MapProvince province = new MapProvince(DataUtil.getUUID(),cellProvince.getStringCellValue(),date,date);
                    MapCity city = new MapCity(DataUtil.getUUID(), province, cellCity.getStringCellValue(), date, date);
                    MapDistrict district = new MapDistrict(DataUtil.getUUID(), city, cellDistrict.getStringCellValue(), date, date);
                    MapTown town = new MapTown(DataUtil.getUUID(), district, cellTown.getStringCellValue(), date, date);
                    ExpressDistribution exp = new ExpressDistribution(town.getId(), town, date, date);
                    expList.add(exp);
                    proList.add(province);
                    cityList.add(city);
                    disList.add(district);
                    townList.add(town);
                    lastTown = town;
                }
            }
            
            if(!proList.isEmpty()){
                areaDAO.addProvince(proList);
                proList.clear();
                areaDAO.addCity(cityList);
                cityList.clear();
                areaDAO.addDistrict(disList);
                disList.clear();
                areaDAO.addTown(townList);
                townList.clear();
                expDAO.addExpressDistribution(expList);
                expList.clear();
            }
            workbook.close();
            fis.close();
//        }catch(Exception ex){
//        //if the file is too big, convert to csv file to read
//        
//        }
    }

    @Override
    public void run() {
        //excel2003 version 
            if(filePath.matches(" ^.*(\\.xls)$")){
                //not support now
            }
            //excel2007+ version
            else{
                try {
                    //try {
                    readXlsx(filePath, fileName, file, areaDAO, expDAO);
//                } catch (FileNotFoundException ex) {
//                    System.out.println("The file can't be found!");
//                }
                } catch (IOException ex) {
                    Logger.getLogger(ExcelUtil.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
    }


    
}
